﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using SAServices.Model;

namespace SAServices.DAL
{
    public class FamilyAttrDAL
    {
        internal static bool AddFamilyAttrInfo(Model.ConnectionConext connCtx, Model.FamilyAttrInfo familyAttrInfo)
        {
            string sql = "INSERT INTO FamilyAttrs(FamilyID, TemplateFileName, TemplateFileBinary, ModelPicName, ModelPicBinary, ModelPicThumbnailBinary, DrawPicName, " +
            " DrawPicBinary, DrawingName, DrawingBinary, ReferenceName, ReferenceBinary, Description, Vendor) Values " +
            "(@familyId, @templateFileName, @templateFileBinary, @modelPicName, @modelPicBinary, @modelPicThumbnailBinary, @drawPicName, " +
            "@drawPicBinary, @drawingName, @drawingBinary, @referenceName, @referenceBinary, @description, @vendor)";
            SqlConnection conn = connCtx.GetOpenSqlConn();
            using (SqlCommand command = conn.CreateCommand())
            {
                command.CommandText = sql;
                command.Parameters.Add(new SqlParameter("@familyId", familyAttrInfo.FamilyID));
                command.Parameters.Add(new SqlParameter("@templateFileName", familyAttrInfo.TemplateFileName));
                command.Parameters.Add(new SqlParameter("@templateFileBinary", familyAttrInfo.TemplateFileBinary));
                command.Parameters.Add(new SqlParameter("@modelPicName", familyAttrInfo.ModelPicName));
                command.Parameters.Add(new SqlParameter("@modelPicBinary", familyAttrInfo.ModelPicBinary));
                command.Parameters.Add(new SqlParameter("@modelPicThumbnailBinary", familyAttrInfo.ModelPicThumbnailBinary));
                command.Parameters.Add(new SqlParameter("@drawPicName", familyAttrInfo.DrawPicName));
                command.Parameters.Add(new SqlParameter("@drawPicBinary", familyAttrInfo.DrawPicBinary));
                command.Parameters.Add(new SqlParameter("@drawingName", familyAttrInfo.DrawingName));
                command.Parameters.Add(new SqlParameter("@drawingBinary", familyAttrInfo.DrawingBinary));
                command.Parameters.Add(new SqlParameter("@referenceName", familyAttrInfo.ReferenceName));
                command.Parameters.Add(new SqlParameter("@referenceBinary", familyAttrInfo.ReferenceBinary));
                command.Parameters.Add(new SqlParameter("@description", familyAttrInfo.Description));
                command.Parameters.Add(new SqlParameter("@vendor", familyAttrInfo.Vendor));

                return command.ExecuteNonQuery() > 0 ? true : false;
            }
        }

        internal static bool UpdateFamilyAttrInfo(Model.ConnectionConext connCtx, Model.FamilyAttrInfo familyAttrInfo)
        {
            string sql = "UPDATE FamilyAttrs SET ModelPicName = @modelPicName, ModelPicBinary = @modelPicBinary , ModelPicThumbnailBinary = @modelPicThumbnailBinary, DrawPicName = @drawPicName, " +
            " DrawPicBinary = @drawPicBinary, DrawingName = @drawingName, DrawingBinary = @drawingBinary, ReferenceName = @referenceName, " +
            " ReferenceBinary = @referenceBinary, Description = @description, Vendor = @vendor WHERE FamilyID = @familyID; ";
            SqlConnection conn = connCtx.GetOpenSqlConn();
            using (SqlCommand command = conn.CreateCommand())
            {
                command.CommandText = sql;
                command.Parameters.Add(new SqlParameter("@familyID", familyAttrInfo.FamilyID));
                //command.Parameters.Add(new SqlParameter("@templateFileName", familyAttrInfo.TemplateFileName));
                //command.Parameters.Add(new SqlParameter("@templateFileBinary", familyAttrInfo.TemplateFileBinary));
                command.Parameters.Add(new SqlParameter("@modelPicName", familyAttrInfo.ModelPicName));
                command.Parameters.Add(new SqlParameter("@modelPicBinary", familyAttrInfo.ModelPicBinary));
                command.Parameters.Add(new SqlParameter("@modelPicThumbnailBinary", familyAttrInfo.ModelPicThumbnailBinary));
                command.Parameters.Add(new SqlParameter("@drawPicName", familyAttrInfo.DrawPicName));
                command.Parameters.Add(new SqlParameter("@drawPicBinary", familyAttrInfo.DrawPicBinary));
                command.Parameters.Add(new SqlParameter("@drawingName", familyAttrInfo.DrawingName));
                command.Parameters.Add(new SqlParameter("@drawingBinary", familyAttrInfo.DrawingBinary));
                command.Parameters.Add(new SqlParameter("@referenceName", familyAttrInfo.ReferenceName));
                command.Parameters.Add(new SqlParameter("@referenceBinary", familyAttrInfo.ReferenceBinary));
                command.Parameters.Add(new SqlParameter("@description", familyAttrInfo.Description));
                command.Parameters.Add(new SqlParameter("@vendor", familyAttrInfo.Vendor));

                return command.ExecuteNonQuery() > 0 ? true : false;
            }
        }

        internal static bool DeleteFamilyAttrInfo(Model.ConnectionConext connCtx, Guid familyId)
        {
            string sql = String.Format("DELETE FROM FamilyAttrs WHERE FamilyID = '{0}'", familyId);

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static Model.FamilyAttrInfo GetFamilyAttrInfo(Model.ConnectionConext connCtx, Guid familyId)
        {
            Model.FamilyAttrInfo retInfo = new Model.FamilyAttrInfo();

            string sql = String.Format("SELECT * FROM FamilyAttrs WHERE FamilyID = '{0}'", familyId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            //FamilyID, TemplateFileName, TemplateFileBinary, ModelPicName, ModelPicBinary, DrawPicName, " +
            //" DrawPicBinary, DrawingName, DrawingBinary, ReferenceName, ReferenceBinary, Description, Vendor

            if (dt.Rows.Count > 0)
            {
               retInfo.TemplateFileName =Convert.ToString(dt.Rows[0]["TemplateFileName"]);
               retInfo.TemplateFileBinary = (Byte[])dt.Rows[0]["TemplateFileBinary"];
               retInfo.ModelPicName = Convert.ToString(dt.Rows[0]["ModelPicName"]);
               retInfo.ModelPicBinary = (Byte[])dt.Rows[0]["ModelPicBinary"];
               retInfo.ModelPicThumbnailBinary = (Byte[])dt.Rows[0]["ModelPicThumbnailBinary"];
               retInfo.DrawPicName = Convert.ToString(dt.Rows[0]["DrawPicName"]);
               retInfo.DrawPicBinary = (Byte[])dt.Rows[0]["DrawPicBinary"];
               retInfo.DrawingName = Convert.ToString(dt.Rows[0]["DrawingName"]);
               retInfo.DrawingBinary = (Byte[])dt.Rows[0]["DrawingBinary"];
               retInfo.ReferenceName = Convert.ToString(dt.Rows[0]["ReferenceName"]);
               retInfo.ReferenceBinary = (Byte[])dt.Rows[0]["ReferenceBinary"];
               retInfo.Description = Convert.ToString(dt.Rows[0]["Description"]);
               retInfo.Vendor = Convert.ToString(dt.Rows[0]["Vendor"]);
               
            }

            return retInfo;
        }

        internal static Model.FamilyAttrInfo GetFamilyAttrInfo(Model.ConnectionConext connCtx, Guid familyId, FamilyAttrType type)
        {
            Model.FamilyAttrInfo retInfo = new Model.FamilyAttrInfo();

            string sql = String.Empty;

            //FamilyID, TemplateFileName, TemplateFileBinary, ModelPicName, ModelPicBinary, DrawPicName, " +
            //" DrawPicBinary, DrawingName, DrawingBinary, ReferenceName, ReferenceBinary, Description, Vendor

            switch (type)
            {
                case FamilyAttrType.kTemplateFile:
                    sql = String.Format("SELECT TemplateFileName, TemplateFileBinary FROM FamilyAttrs WHERE FamilyID = '{0}'", familyId);
                    break;
                case FamilyAttrType.kModelPic:
                    sql = String.Format("SELECT ModelPicName, ModelPicBinary, ModelPicThumbnailBinary FROM FamilyAttrs WHERE FamilyID = '{0}'", familyId);
                    break;
                case FamilyAttrType.kDrawPic:
                    sql = String.Format("SELECT DrawPicName, DrawPicBinary FROM FamilyAttrs WHERE FamilyID = '{0}'", familyId);
                    break;
                case FamilyAttrType.kDrawing:
                    sql = String.Format("SELECT DrawingName, DrawingBinary FROM FamilyAttrs WHERE FamilyID = '{0}'", familyId);
                    break;
                case FamilyAttrType.kReference:
                    sql = String.Format("SELECT ReferenceName, ReferenceBinary FROM FamilyAttrs WHERE FamilyID = '{0}'", familyId);
                    break;
                case FamilyAttrType.kDescription:
                    sql = String.Format("SELECT Description FROM FamilyAttrs WHERE FamilyID = '{0}'", familyId);
                    break;
                case FamilyAttrType.kVendor:
                    sql = String.Format("SELECT Vendor FROM FamilyAttrs WHERE FamilyID = {0}", familyId);
                    break;
                default:
                    break;
            }

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            //FamilyID, TemplateFileName, TemplateFileBinary, ModelPicName, ModelPicBinary, DrawPicName, " +
            //" DrawPicBinary, DrawingName, DrawingBinary, ReferenceName, ReferenceBinary, Description, Vendor

            if (dt.Rows.Count > 0)
            {
                switch (type)
                {
                    case FamilyAttrType.kTemplateFile:
                        retInfo.TemplateFileName = Convert.ToString(dt.Rows[0]["TemplateFileName"]);
                        retInfo.TemplateFileBinary = (Byte[])dt.Rows[0]["TemplateFileBinary"];
                        break;
                    case FamilyAttrType.kModelPic:
                        retInfo.ModelPicName = Convert.ToString(dt.Rows[0]["ModelPicName"]);
                        retInfo.ModelPicBinary = (Byte[])dt.Rows[0]["ModelPicBinary"];
                        retInfo.ModelPicThumbnailBinary = (Byte[])dt.Rows[0]["ModelPicThumbnailBinary"];
                        break;
                    case FamilyAttrType.kDrawPic:
                        retInfo.DrawPicName = Convert.ToString(dt.Rows[0]["DrawPicName"]);
                        retInfo.DrawPicBinary = (Byte[])dt.Rows[0]["DrawPicBinary"];
                        break;
                    case FamilyAttrType.kDrawing:
                        retInfo.DrawingName = Convert.ToString(dt.Rows[0]["DrawingName"]);
                        retInfo.DrawingBinary = (Byte[])dt.Rows[0]["DrawingBinary"];
                        break;
                    case FamilyAttrType.kReference:
                        retInfo.ReferenceName = Convert.ToString(dt.Rows[0]["ReferenceName"]);
                        retInfo.ReferenceBinary = (Byte[])dt.Rows[0]["ReferenceBinary"];
                        break;
                    case FamilyAttrType.kDescription:
                        retInfo.Description = Convert.ToString(dt.Rows[0]["Description"]);
                        break;
                    case FamilyAttrType.kVendor:
                        retInfo.Vendor = Convert.ToString(dt.Rows[0]["Vendor"]);
                        break;
                    default:
                        break;
                }
            }

            return retInfo;
        }

        internal static byte[] GetFamilyThumbnailPic(ConnectionConext connCtx, Guid familyId)
        {
            byte[] retVal = new byte[0];
            string sql = String.Format("SELECT ModelPicThumbnailBinary FROM FamilyAttrs WHERE FamilyID = '{0}'", familyId);
            DataTable dt = GenDAL.GetTable(connCtx, sql);

            if (dt.Rows.Count > 0)
            {
                retVal = (Byte[])dt.Rows[0]["ModelPicThumbnailBinary"];
            }

            return retVal;
        }
    }
}
